<?php
/* -----------------------------------------------------------------
* 	ID:						$Id: commerce_seo.inc.php 2 2011-02-17 13:35:25Z akausch $
* 	Letzter Stand:			$Revision: 2 $
* 	zuletzt geändert von: 	$Author: akausch $
* 	Datum:					$Date: 2011-02-17 14:35:25 +0100 (Do, 17. Feb 2011) $
*
* 	commerce:SEO by Webdesign Erfurt
* 	http://www.commerce-seo.de
*
* 	Copyright (c) since 2010 commerce:SEO
* ------------------------------------------------------------------
* 	based on:
* 	(c) 2000-2001 The Exchange Project  (earlier name of osCommerce)
* 	(c) 2002-2003 osCommerce - www.oscommerce.com
* 	(c) 2003     nextcommerce - www.nextcommerce.org
* 	(c) 2005     xt:Commerce - www.xt-commerce.com
*
* 	Released under the GNU General Public License
* --------------------------------------------------------------- */

class CommerceSeo {

	function blog_da() {
		$ist_blog_da = 'blog.php';
		if(file_exists($ist_blog_da))
			return true;
		else
			return false;
	}

	function CommerceSeo() {
		require_once(DIR_FS_INC . 'xtc_get_product_path.inc.php');
		require_once(DIR_FS_INC . 'xtc_get_category_path.inc.php');
		require_once(DIR_FS_INC . 'xtc_get_parent_categories.inc.php');
		require_once(DIR_FS_INC . 'cseo_get_url_friendly_text.inc.php');
	}

	function getProductLink($parameters,$connection='NONSSL',$language) {
		$explodedParams=explode('&',$parameters);
		foreach($explodedParams as $value) {
			if (substr($value,0,12) == 'products_id=') {
				$productId=substr($value,12,strlen($value));
			}
		}

		if ($connection == 'SSL' && ENABLE_SSL)
        	$link = HTTPS_SERVER.DIR_WS_CATALOG;
        else
            $link = HTTP_SERVER.DIR_WS_CATALOG ;


		$product_link_query   = "SELECT url_text FROM commerce_seo_url WHERE products_id = '".$productId."' AND language_id='".$language."'";
		$product_link_query   = xtDBquery($product_link_query);
		$product_link		  = xtc_db_fetch_array($product_link_query,false);

		return $link.$product_link['url_text'].'.html';
	}

	function getCategoryLink($parameters, $connection='NONSSL', $language) {
		$explodedParams=explode('&',$parameters);

		foreach($explodedParams as $value) {
			if (substr($value,0,6) == 'cPath=') {
				$xtcCategoryParameter = substr($value,6,strlen($value));
				if(strpos($xtcCategoryParameter,'_')) {
					$categoryId = substr(substr($value,6,strlen($value)),strrpos($xtcCategoryParameter,'_')+1);
				} else {
				$categoryId = substr($value,6,strlen($value));
				}
			}
		}
		if ($connection == 'SSL' && ENABLE_SSL) {
        	$link = HTTPS_SERVER.DIR_WS_CATALOG;
        } else {
            $link = HTTP_SERVER.DIR_WS_CATALOG ;
        }

		$category_link_query   = "SELECT url_text FROM commerce_seo_url WHERE categories_id = '".$categoryId."' AND language_id='".$language."'";
		$category_link_query   = xtDBquery($category_link_query);
		$category_link		  = xtc_db_fetch_array($category_link_query,false);
		$params = '';
		foreach($explodedParams as $value) {
			if(substr($value,0,9) == 'per_site=') {
				$perSite = substr($value,9,strlen($value));
				if(!empty($params))
					$sep = '&';
				$params .= $sep.'per_site='.$perSite;
			}
			if(substr($value,0,8) == 'view_as=') {
				$viewAs = substr($value,8,strlen($value));
				if(!empty($params))
					$sep = '&';
				$params .= $sep.'view_as='.$viewAs;
			}
			if(substr($value,0,10) == 'filter_id=') {
				$filterId = substr($value,10,strlen($value));
				if(!empty($params))
					$sep = '&';
				$params .= $sep.'filter_id='.$filterId;
			}
			if(substr($value,0,10) == 'multisort=') {
				$multisort = substr($value,10,strlen($value));
				if(!empty($params))
					$sep = '&';
				$params .= $sep.'multisort='.$multisort;
			}
		}

		return $link.$category_link['url_text'].(!empty($params)?'?'.$params:'');
	}

	function getContentLink($parameters,$connection='NONSSL',$language) {
		$explodedParams=explode('&',$parameters);

		// Extract category id and file parameter
		foreach($explodedParams as $value) {
			if (substr($value,0,4) == 'coID') {
				$contentGroupId=substr($value,5,strlen($value));
			}
		}

		if ($connection == 'SSL' && ENABLE_SSL) {
        	$link = HTTPS_SERVER.DIR_WS_CATALOG;
        } else {
            $link = HTTP_SERVER.DIR_WS_CATALOG;
        }

		$content_link_query   = "SELECT url_text FROM commerce_seo_url WHERE content_group = '".$contentGroupId."' AND language_id='".$language."'";
		$content_link_query   = xtDBquery($content_link_query);
		$content_link		  = xtc_db_fetch_array($content_link_query,false);

		return $link.$content_link['url_text'].'.html';

	}

	function getBlogLink($parameters,$connection='NONSSL',$language) {
		$explodedParams=explode('&',$parameters);

		foreach($explodedParams as $value) {
			if (substr($value,0,9) == 'blog_item') {
				$blogID = substr($value,10,strlen($value));
			}
		}

		if ($connection == 'SSL' && ENABLE_SSL) {
        	$link = HTTPS_SERVER.DIR_WS_CATALOG;
        } else {
            $link = HTTP_SERVER.DIR_WS_CATALOG;
        }

		$blog_link_query   = "SELECT url_text FROM commerce_seo_url WHERE blog_id = '".$blogID."' AND language_id='".$language."'";
		$blog_link_data   = xtDBquery($blog_link_query);
		$blog_link		  = xtc_db_fetch_array($blog_link_data,false);

		return $link.$blog_link['url_text'].'.html';
	}

	function getBlogCatLink($parameters,$connection='NONSSL',$language) {
		$explodedParams=explode('&',$parameters);

		foreach($explodedParams as $value) {
			if (substr($value,0,8) == 'blog_cat') {
				$blogCatID = substr($value,9,strlen($value));
			}
		}

		if ($connection == 'SSL' && ENABLE_SSL) {
        	$link = HTTPS_SERVER.DIR_WS_CATALOG;
        } else {
            $link = HTTP_SERVER.DIR_WS_CATALOG;
        }

		$blog_cat_link_query   = "SELECT url_text FROM commerce_seo_url WHERE blog_cat = '".$blogCatID."' AND language_id='".$language."'";
		$blog_cat_link_data   = xtDBquery($blog_cat_link_query);
		$blog_cat_link		  = xtc_db_fetch_array($blog_cat_link_data,false);

		return $link.$blog_cat_link['url_text'];
	}

	function getLanguageChangeLink($page,$parameters,$connection='NONSSL') {

		$languageParamStartPos = strpos($parameters,'language=');
		$language=substr($parameters,($languageParamStartPos+9),2);
		$languageId_query   = "SELECT languages_id
								FROM languages
								WHERE code='".$language."'";
		$languageId_query   = xtDBquery($languageId_query);
		$languageId_result	= xtc_db_fetch_array($languageId_query,false);

		if ($connection == 'SSL' && ENABLE_SSL) {
        	$link = HTTPS_SERVER.DIR_WS_CATALOG;
        } else {
            $link = HTTP_SERVER.DIR_WS_CATALOG ;
        }

		$explodedParams=explode('&',$parameters);
		switch ($page) {
			case 'product_info.php':
				foreach($explodedParams as $value) {
					if (substr($value, 0, 12) == 'products_id=') {
						$categoryId=' IS NULL';
						$productId='=\''.substr($value, 12, strlen($value)).'\'';
						if($this->blog_da()) {
						$blogID = ' IS NULL';
						$blogCAT = ' IS NULL';}
						$coID=' IS NULL';
					}
				}
				break;
			case 'index.php':
				$catIdFound=false;
				foreach($explodedParams as $value) {
					if (substr($value, 0, 6) == 'cPath=') {
						$categoryId='=\''.substr($value, 6, strlen($value)).'\'';
						$productId=' IS NULL';
						$coID=' IS NULL';
						if($this->blog_da()){
						$blogID = ' IS NULL';
						$blogCAT = ' IS NULL';}
						$catIdFound=true;
					}
				}

				if (!$catIdFound) {
					return $link.$language;
				}
				break;
			case 'blog.php' :
				foreach($explodedParams as $value) {
					if (substr($value, 0, 10) == 'blog_item=') {
						$blogID = '=\''.substr($value, 10, strlen($value)).'\'';
						$categoryId=' IS NULL';
						$productId=' IS NULL';
						$coID=' IS NULL';
					}
				}
			case 'shop_content.php':
				foreach($explodedParams as $value) {
					if (substr($value, 0, 5) == 'coID=') {
						$categoryId=' IS NULL';
						$productId=' IS NULL';
						if($this->blog_da()){
						$blogID = ' IS NULL';
						$blogCAT = ' IS NULL';}
						$coID='=\''.substr($value, 5, strlen($value)).'\'';
					}
				}
				break;

			case 'commerce_seo_url.php':
				if(substr_count($parameters,'products_id=') > 0)
				foreach($explodedParams as $value) {
					if (substr($value,0,12) == 'products_id=') {
						$categoryId=' IS NULL';
						$productId='=\''.substr($value,12,strlen($value)).'\'';
						if($this->blog_da()){
						$blogID = ' IS NULL';
						$blogCAT = ' IS NULL';}
						$coID=' IS NULL';
					}
				}
				elseif (substr_count($parameters,'cPath=') > 0 && substr_count($parameters,'blog_cat=') == 0)
					foreach($explodedParams as $value) {
						if (substr($value, 0, 6) == 'cPath=') {
							$categoryId='=\''.substr($value, 6, strlen($value)).'\'';
							$productId=' IS NULL';
							$coID=' IS NULL';
						if($this->blog_da()){
						$blogID = ' IS NULL';
						$blogCAT = ' IS NULL';}
							$catIdFound=true;
						}
					}
				elseif (substr_count($parameters,'coID=') > 0)
					foreach($explodedParams as $value) {
						if (substr($value,0,5) == 'coID=') {
							$categoryId=' IS NULL';
							$productId=' IS NULL';
							if($this->blog_da()){
							$blogID = ' IS NULL';
							$blogCAT = ' IS NULL';}
							$coID='=\''.substr($value,5,strlen($value)).'\'';
						}
					}
				elseif (substr_count($parameters,'blog_item') > 0) {
				foreach($explodedParams as $value) {
					if (substr($value,0,10) == 'blog_item=') {
						$blogID = '=\''.substr($value,10,strlen($value)).'\'';
						$blogCAT = ' IS NULL';
						$categoryId=' IS NULL';
						$productId=' IS NULL';
						$coID=' IS NULL';
					}
				}
				}
				elseif (substr_count($parameters,'blog_cat=') > 0)
				foreach($explodedParams as $value) {
					if (substr($value,0,9) == 'blog_cat=') {
						$blogCAT = '=\''.substr($value,9,strlen($value)).'\'';
						$blogID = ' IS NULL';
						$categoryId=' IS NULL';
						$productId=' IS NULL';
						$coID=' IS NULL';
					}
				}
				break;
		}

		if($this->blog_da())
			$blog_request = "AND blog_cat".$blogCAT." AND blog_id".$blogID;
		else
			$blog_request = '';

		$link_query   = "SELECT url_text
						FROM commerce_seo_url
						WHERE 1>0
						AND products_id".$productId."
						AND categories_id".$categoryId."
						AND content_group".$coID."
						".$blog_request."
						AND language_id=".$languageId_result['languages_id'];

		$link_query   = xtDBquery($link_query);
		$link_result  = xtc_db_fetch_array($link_query,false);
		switch ($page) {
			// product
			case 'product_info.php':
				return $link.$link_result['url_text'].'.html';
				break;
			// Category
			case 'index.php':
				return $link.$link_result['url_text'];
				break;
			// Content
			case 'shop_content.php':
				return $link.$link_result['url_text'].'.html';
				break;
			// Blog
			case 'blog.php':
				if($this->blog_da())
					return $link.$link_result['url_text'].'.html';
				break;
			case 'commerce_seo_url.php':
				if ($productId != ' IS NULL' && $link_result['url_text'] != '')
					return $link.$link_result['url_text'].'.html';
				elseif($categoryId != ' IS NULL' && $link_result['url_text'] != '')
					return $link.$link_result['url_text'];
				elseif($coID != ' IS NULL' && $link_result['url_text'] != '')
					return $link.$link_result['url_text'].'.html';
				elseif($blogCAT != ' IS NULL' && $link_result['url_text'] != '')
					return $link.$link_result['url_text'];
				elseif($blogID != ' IS NULL' && $link_result['url_text'] != '')
					return $link.$link_result['url_text'].'.html	';
				else {
					return $link.$language;
				}
				break;
			}

	}

	function getCategoryPathForProduct($productId,$language) {
		$xtcProductPath = xtc_get_product_path($productId);
		$pathExploded = explode('_',$xtcProductPath);
		foreach($pathExploded as $value) {
			$productPath .= $this->getCategoryNameForId($value, $language);
			$pathExploded[0] <> '' ? $productPath .= '/' : false;
		}
		return $productPath;
	}

	function getCategoryPathForCategory($categoryId, $language) {
		$xtcCategoryPath = xtc_get_category_path($categoryId);
		$pathExploded = explode('_',$xtcCategoryPath);
		foreach($pathExploded as $value) {
			$pathExploded[0] <> '' ? $categoryPath .= '/' : false;
			$categoryPath .= $this->getCategoryNameForId($value, $language);
		}
		return $categoryPath.'/';
	}

	function getCategoryNameForId($categoryId,$language) {
    	$category_query   = "SELECT categories_name, categories_url_alias
    							FROM " . TABLE_CATEGORIES_DESCRIPTION . "
    							WHERE categories_id = '" . $categoryId . "' and language_id = '" . $language . "'";
   	 	$category_query   = xtDBquery($category_query);
		$category         = xtc_db_fetch_array($category_query,false);
		if($category['categories_url_alias'] !='')
			$cat_name = $category['categories_url_alias'];
		else
			$cat_name = $category['categories_name'];
   		return cseo_get_url_friendly_text($cat_name);
	}

	function createSeoDBTable () {

		$useLanguageUrl=false;

		$commerce_seo_query   = "TRUNCATE TABLE `commerce_seo_url`";
   	 	$commerce_seo_query   = xtDBquery($commerce_seo_query);

		// ******* Products Index ********//
		$product_query   = "SELECT
								pd.products_id,
								pd.language_id,
								pd.products_name,
								pd.products_url_alias,
								l.code
							FROM
								" . TABLE_PRODUCTS_DESCRIPTION ." pd,
								". TABLE_LANGUAGES ." l
							WHERE
								pd.language_id = l.languages_id";
   	 	$product_query = xtDBquery($product_query);

		if ($_REQUEST['configuration']['MODULE_COMMERCE_SEO_INDEX_LANGUAGEURL'] == 'True')
			$useLanguageUrl=true;
		elseif(!$_REQUEST['configuration']['MODULE_COMMERCE_SEO_INDEX_LANGUAGEURL'] && MODULE_COMMERCE_SEO_INDEX_LANGUAGEURL=='True')
			$useLanguageUrl=true;

		while ($productList = xtc_db_fetch_array($product_query, false)) {
			if(MODULE_COMMERCE_SEO_URL_LENGHT == 'True')
				$productPath = '';
			else
				$productPath = $this->getCategoryPathForProduct($productList['products_id'],$productList['language_id']);

			if($productList['products_url_alias'] !='')
				$products_name = $productList['products_url_alias'];
			else
				$products_name = $productList['products_name'];

			if ($useLanguageUrl)
				$productLink = $productList['code'].'/'. $productPath .cseo_get_url_friendly_text($products_name);
			else
				$productLink = $productPath .cseo_get_url_friendly_text($products_name);


			$productLink = $this->validateDBKeyLink ($productLink,'');

			$product_seo_query   = "INSERT INTO commerce_seo_url (url_md5,url_text,products_id,language_id) VALUES ('".md5($productLink)."','".$productLink."','".$productList['products_id']."','".$productList['language_id']."')";
			$product_seo_query   = xtDBquery($product_seo_query);
		}

		// ******* Language ********//
		if($useLanguageUrl) {
			$languages_query = "SELECT code,languages_id FROM ". TABLE_LANGUAGES;
			$languages_query = xtDBquery($languages_query);

			while($lang = xtc_db_fetch_array($languages_query)) {
				$lang_seo_query   = "INSERT INTO commerce_seo_url (url_md5,url_text,language_id) VALUES ('".md5($lang['code'])."','".$lang['code']."','".$lang['languages_id']."')";
				$lang_seo_query   = xtDBquery($lang_seo_query);
			}
		}

		// ******* Categories Index ********//
		$category_query   = "SELECT
								cd.categories_id,
								cd.language_id,
								cd.categories_name,
								cd.categories_url_alias,
								l.code
							FROM
								" . TABLE_CATEGORIES_DESCRIPTION . " cd,
								". TABLE_LANGUAGES ." l
							WHERE
								cd.language_id = l.languages_id";
   	 	$category_query   = xtDBquery($category_query);

		// Kategorien durchlaufen und Indexierung fР вЂ™РЎвЂr SEO Tabelle vornehmen
		while ($categoryList = xtc_db_fetch_array($category_query, false)) {
			$categoryPath = $this->getCategoryPathForCategory($categoryList['categories_id'], $categoryList['language_id']);
			if($useLanguageUrl)
				$categoryLink = $categoryList['code'] . $categoryPath;
			else
				$categoryLink = substr($categoryPath,1);

			$categoryLink = $this->validateDBKeyLink ($categoryLink,'');

			$category_seo_query   = "INSERT INTO commerce_seo_url (url_md5,url_text,categories_id,language_id) VALUES ('".md5($categoryLink)."','".$categoryLink."','".$categoryList['categories_id']."','".$categoryList['language_id']."')";
			$category_seo_query   = xtDBquery($category_seo_query);
		}

		if($this->blog_da()) {
			// ******* Blog Kategorie Indexierung  ********//
			$blog_query   = "SELECT
								bc.categories_id as blog_cat_id,
								l.code AS code,
								bc.language_id as blog_cat_lang,
								bc.titel as blog_cat_titel
							FROM
								".TABLE_BLOG_CATEGORIES." bc,
								".TABLE_LANGUAGES." l
							WHERE
								bc.language_id = l.languages_id";
			$blog_data   = xtDBquery($blog_query);

			// Blog Datensaetze durchlaufen und Indexierung fР вЂњРЎВr SEO Tabelle vornehmen
			while ($blogList = xtc_db_fetch_array($blog_data, false)) {
				if ($useLanguageUrl)
					$blogLink = $blogList['code'].'/'.cseo_get_url_friendly_text($blogList['blog_cat_titel']);
				else
					$blogLink = cseo_get_url_friendly_text($blogList['blog_cat_titel']);

				$blogLink = $this->validateDBKeyLink ($blogLink,'');

				$blog_seo_query = "INSERT INTO commerce_seo_url (url_md5,url_text,blog_cat,language_id) VALUES ('".md5($blogLink)."','".$blogLink."','".$blogList['blog_cat_id']."','".$blogList['blog_cat_lang']."')";
				$blog_seo_query = xtDBquery($blog_seo_query);
			}

			// ******* Blog Indexierung  ********//
			$blog_query   = "SELECT
								bi.item_id as blog_item_id,
								bi.language_id as blog_item_lang,
								bi.name AS blog_item_title,
								bc.categories_id as blog_cat_id,
								l.code AS code,
								bc.titel as blog_cat_titel
							FROM
								".TABLE_BLOG_ITEMS." bi,
							 	".TABLE_BLOG_CATEGORIES." bc,
							 	".TABLE_LANGUAGES." l
							 WHERE
							 	bi.language_id = l.languages_id
							 AND
							 	bc.language_id = bi.language_id
							 AND
							 	bi.categories_id = bc.categories_id";
			$blog_data   = xtDBquery($blog_query);

			while ($blogList = xtc_db_fetch_array($blog_data, false)) {
				if ($useLanguageUrl)
					$blogLink = $blogList['code'].'/'.cseo_get_url_friendly_text($blogList['blog_cat_titel']).'/'.cseo_get_url_friendly_text($blogList['blog_item_title']);
				else
					$blogLink = cseo_get_url_friendly_text($blogList['blog_cat_titel']).'/'.cseo_get_url_friendly_text($blogList['blog_item_title']);

				$blogLink = $this->validateDBKeyLink ($blogLink,'');

				$blog_seo_query = "INSERT INTO commerce_seo_url (url_md5,url_text,blog_id,language_id) VALUES ('".md5($blogLink)."','".$blogLink."','".$blogList['blog_item_id']."','".$blogList['blog_item_lang']."')";
				$blog_seo_query = xtDBquery($blog_seo_query);
			}
		}

		// ******* Content Index ********//
		$content_query = "SELECT
								cm.content_group,
								cm.languages_id,
								cm.content_title,
								cm.content_url_alias,
								l.code
							FROM
								" . TABLE_CONTENT_MANAGER . " cm,
								". TABLE_LANGUAGES ." l
							WHERE
								cm.languages_id = l.languages_id";
   	 	$content_query = xtDBquery($content_query);

		// Content DatensР вЂњР’В¤tze durchlaufen und Indexierung fР вЂњРЎВr SEO Tabelle vornehmen
		while ($contentList = xtc_db_fetch_array($content_query, false)) {
			if($contentList['content_url_alias'] !='')
				$content_url = $contentList['content_url_alias'];
			else
				$content_url = $contentList['content_title'];

			if ($useLanguageUrl)
				$contentLink = $contentList['code'].'/'.cseo_get_url_friendly_text($content_url);
			else
				$contentLink = cseo_get_url_friendly_text($content_url);

			$contentLink = $this->validateDBKeyLink ($contentLink,'');

			$content_seo_query = "INSERT INTO commerce_seo_url (url_md5,url_text,content_group,language_id) VALUES ('".md5($contentLink)."','".$contentLink."','".$contentList['content_group']."','".$contentList['languages_id']."')";
			$content_seo_query = xtDBquery($content_seo_query);
		}
	}

	function updateSeoDBTable($elementType, $operation, $id) {

		switch ($elementType) {
			// ******* Product Update ********//
			case 'product':
				$result_query   = "SELECT
										pd.products_id,pd.language_id,
										pd.products_name,
										pd.products_url_alias,
										l.code
									FROM
										" . TABLE_PRODUCTS_DESCRIPTION ." pd,
										". TABLE_LANGUAGES ." l
									WHERE
										pd.products_id=".$id."
									AND
										pd.language_id  = l.languages_id";
		   	 	$result_query   = xtDBquery($result_query);

				while ($resultList = xtc_db_fetch_array($result_query, false)) {
					if(MODULE_COMMERCE_SEO_URL_LENGHT == 'True')
						$productPath = '';
					else
						$productPath = $this->getCategoryPathForProduct($resultList['products_id'],$resultList['language_id']);

					if($resultList['products_url_alias'] !='')
						$product_name = $resultList['products_url_alias'];
					else
						$product_name = $resultList['products_name'];

					if (MODULE_COMMERCE_SEO_INDEX_LANGUAGEURL=='True')
						$productLink = $resultList['code'].'/'. $productPath .cseo_get_url_friendly_text($product_name);
					else
						$productLink = $productPath .cseo_get_url_friendly_text($product_name);

					$old_produkt_link = xtc_db_fetch_array(xtDBquery("SELECT url_text FROM commerce_seo_url WHERE products_id = '".$id."' AND language_id = '".$resultList['language_id']."'"));

					if($productLink != $old_produkt_link['url_text']) {
						$productPath = $this->validateDBKeyLink ($productLink,'');
						$commerce_update_seo_query   = "UPDATE commerce_seo_url SET url_md5='".md5($productPath)."', url_text='".$productLink."' WHERE products_id='".$id."' AND language_id='".$resultList['language_id']."'";
						$commerce_update_seo_query   = xtDBquery($commerce_update_seo_query);
					}
				}
				break;

			// ******* Category Update ********//
			case 'category':
				$result_query   = "SELECT
										cd.categories_id,
										cd.language_id,
										l.code
									FROM
										" . TABLE_CATEGORIES_DESCRIPTION . " cd,
										". TABLE_LANGUAGES ." l
									WHERE
										cd.categories_id = '".$id."'
									AND
										cd.language_id = l.languages_id";
   	 			$result_query   = xtDBquery($result_query);

				while ($resultList = xtc_db_fetch_array($result_query, false)) {
					$categoryPath = $this->getCategoryPathForCategory($resultList['categories_id'], $resultList['language_id']);
					if (MODULE_COMMERCE_SEO_INDEX_LANGUAGEURL=='True')
						$categoryLink = $resultList['code'] . $categoryPath;
					else
						$categoryLink = substr($categoryPath, 1);

					$old_categorylink = xtc_db_fetch_array(xtDBquery(" SELECT url_text
																			FROM commerce_seo_url
																			WHERE categories_id='".$id."'
																			AND language_id='".$resultList['language_id']."'"));

					if($categoryLink != $old_categorylink['url_text']) {
					$categoryLink = $this->validateDBKeyLink ($categoryLink,'');
					$commerce_update_seo_query   = "UPDATE commerce_seo_url SET url_md5='".md5($categoryLink)."', url_text='".$categoryLink."' WHERE categories_id='".$id."' AND language_id='".$resultList['language_id']."'";
					$commerce_update_seo_query   = xtDBquery($commerce_update_seo_query);
					}
				}
				break;

			case 'content':
				$content_query = "SELECT
										cm.content_group,
										cm.languages_id,
										cm.content_title,
										cm.content_url_alias,
										l.code
									FROM
										" . TABLE_CONTENT_MANAGER . " cm,
										". TABLE_LANGUAGES ." l
									WHERE
										cm.content_group = '".$id."'
									AND
										cm.languages_id = l.languages_id";

		   	 	$content_query = xtDBquery($content_query);

				while($content = xtc_db_fetch_array($content_query, false)) {
					if($contentList['content_url_alias'] !='')
						$content_url = $content['content_url_alias'];
					else
						$content_url = $content['content_title'];

					if($useLanguageUrl)
						$contentLink = $content['code'].'/'.cseo_get_url_friendly_text($content_url);
					else
						$contentLink = cseo_get_url_friendly_text($content_url);

					$contentLink = $this->validateDBKeyLink ($contentLink,'');

					$content_seo_query = xtDBquery("UPDATE commerce_seo_url SET url_md5 = '".md5($contentLink)."', url_text = '".$contentLink."' WHERE content_group = '".$id."' AND language_id='".$content['languages_id']."'");
				}

				break;
			// ******* Blog Update ********//
			case 'blog':
				if($this->blog_da()) {
					// ******* Get Blog Information ********//
					$result_query   = "SELECT
									bi.id as blog_item_id,
									bi.language_id as blog_item_lang,
									bi.title AS blog_item_title,
									bc.id as blog_cat_id, l.code AS code,
									bc.titel as blog_cat_titel
									FROM ".TABLE_BLOG_ITEMS." bi,
								 	".TABLE_BLOG_CATEGORIES." bc,
								 	".TABLE_LANGUAGES." l
								 	WHERE bi.language_id = l.languages_id
								 	AND bc.language_id = bi.language_id
								 	AND bi.categories_id = bc.id";
	   	 			$result_query   = xtDBquery($result_query);

					while ($blogList = xtc_db_fetch_array($result_query, false)) {
						// URL mit oder ohne ISO Code anlegen
						if (MODULE_COMMERCE_SEO_INDEX_LANGUAGEURL=='True') {
							$blogLink = $blogList['code'] . cseo_get_url_friendly_text($blogList['blog_cat_titel']).'/'.cseo_get_url_friendly_text($blogList['blog_item_title']);
						} else {
						// Remove leading Slash from URL (/)
							$blogLink = cseo_get_url_friendly_text($blogList['blog_cat_titel']).'/'.cseo_get_url_friendly_text($blogList['blog_item_title']);
						}

						// Update Dataset of this element
						$blogLink = $this->validateDBKeyLink ($blogLink,'');
						$commerce_update_seo_query   = "UPDATE commerce_seo_url	SET url_md5 = '".md5($blogLink)."', url_text = '".$blogLink."' WHERE blog_id = '".$blogList['blog_item_id']."' AND language_id = '".$blogList['language_id']."'";
						$commerce_update_seo_query   = xtDBquery($commerce_update_seo_query);
					}
				}
				break;
		}
	}

	function insertSeoDBTable ($elementType) {
		// Create type-depending URL
		switch ($elementType) {

			// ******* Insert Product ********//
			case 'product':
				// Ermitteln, welche Produkte existieren, die noch nicht in commerce_seo_url indiziert sind
				$result_query   = "SELECT pd.products_id,
										pd.language_id,
										pd.products_name,
										pd.products_url_alias,
										l.code
										FROM " . TABLE_PRODUCTS_DESCRIPTION ." pd
										LEFT JOIN commerce_seo_url seourl
										ON pd.products_id = seourl.products_id
										INNER JOIN ". TABLE_LANGUAGES ." l
										ON pd.language_id = l.languages_id
										WHERE seourl.products_id IS NULL";

		   	 	$result_query   = xtDBquery($result_query);

				// Anlegen der neuen DatensР вЂњР’В¤tze
				while ($resultList = xtc_db_fetch_array($result_query, false)) {
					if(MODULE_COMMERCE_SEO_URL_LENGHT == 'True')
						$productPath = '';
					else
						$productPath = $this->getCategoryPathForProduct($resultList['products_id'],$resultList['language_id']);

					if($resultList['products_url_alias'] !='')
						$product_name = $resultList['products_url_alias'];
					else
						$product_name = $resultList['products_name'];

					// URL mit oder ohne ISO Code anlegen
					if (MODULE_COMMERCE_SEO_INDEX_LANGUAGEURL=='True') {
						$productLink = $resultList['code'].'/'. $productPath .cseo_get_url_friendly_text($product_name);
					} else {
						$productLink = $productPath .cseo_get_url_friendly_text($product_name);
					}

					$productLink = $this->validateDBKeyLink ($productLink,'');
					$product_seo_query   = "INSERT INTO commerce_seo_url (url_md5,url_text,products_id,language_id) VALUES ('".md5($productLink)."','".$productLink."','".$resultList['products_id']."','".$resultList['language_id']."')";
					$product_seo_query   = xtDBquery($product_seo_query);
				}
			break;

			// ******* Insert Category ********//
			case 'category':
				// Ermitteln, welche Kategorien existieren, die noch nicht in commerce_seo_url indiziert sind
				$result_query   = "SELECT cd.categories_id,
											cd.language_id,
											cd.categories_name,
											l.code
										   FROM " . TABLE_CATEGORIES_DESCRIPTION ." cd
										   LEFT JOIN commerce_seo_url AS cseo
										   ON cd.categories_id = cseo.categories_id
										   INNER JOIN ". TABLE_LANGUAGES ." l
										   ON cd.language_id = l.languages_id
										   WHERE cseo.categories_id IS NULL";
		   	 	$result_query   = xtDBquery($result_query);

				while ($resultList = xtc_db_fetch_array($result_query, false)) {
					$categoryPath = $this->getCategoryPathForCategory($resultList['categories_id'], $resultList['language_id']);

					// URL mit oder ohne ISO Code anlegen
					if (MODULE_COMMERCE_SEO_INDEX_LANGUAGEURL=='True') {
						$categoryLink = $resultList['code'] . $categoryPath;
					} else {
						// Remove leading Slash from URL (/)
						$categoryLink = substr($categoryPath,1);
					}

					$categoryLink = $this->validateDBKeyLink ($categoryLink,'');

					$category_seo_query   = "INSERT INTO commerce_seo_url (url_md5,url_text,categories_id,language_id) VALUES ('".md5($categoryLink)."','".$categoryLink."','".$resultList['categories_id']."','".$resultList['language_id']."')";
					$category_seo_query   = xtDBquery($category_seo_query);
				}
			break;

		}
	}

	function validateDBKeyLink ($urlKey,$counter) {
		$product_query   = "SELECT url_text FROM commerce_seo_url where url_md5='".md5($urlKey.$counter)."' ";
		$product_query   = xtDBquery($product_query);

		if (xtc_db_num_rows($product_query)==0) {
			return $urlKey.$counter;
		} else {
			$counter++;
			return $this->validateDBKeyLink ($urlKey,$counter);
		}
	}

	function getIdForURL($linkUrl,$type) {
		if($this->blog_da())
		$query   = "SELECT products_id, categories_id, content_group, blog_id FROM commerce_seo_url WHERE url_md5 = '" . md5($linkUrl) . "' LIMIT 0,1";
		else
		$query   = "SELECT products_id, categories_id, content_group FROM commerce_seo_url WHERE url_md5 = '" . md5($linkUrl) . "' LIMIT 0,1";
   	 	$query   = xtDBquery($query);
		$id       = xtc_db_fetch_array($query);

		// Check the URL type
		switch ($type) {
			case 'product':
				return $id['products_id'];
			case 'category':
				return $id['categories_id'];
			case 'content':
				return $id['content_group'];
			case 'blog':
				return $id['blog_id'];
		}
	}

	function getIdForXTCSumaFriendlyURL($fileName) {
		if (($fileName=='product_info.php' && $_GET['products_id']<>'' && $_GET['action']=='') || ($fileName == 'index.php' && $_GET['cat']<>'' && $_GET['page']=='' && $_GET['action']=='') || ($fileName == 'index.php' && $_GET['cPath']<>'' && $_GET['page']=='' && $_GET['action']=='') || ($fileName == 'shop_content.php' && $_GET['coID']<>'' && $_GET['action']=='')) {
			require_once(DIR_WS_CLASSES.'language.php');
			!$temp_lng ? $temp_lng = new language(xtc_input_validation($_GET['language'], 'char', '')) : false;

			if (!isset ($_SESSION['language']) || isset ($_GET['language'])) {

				if (!isset ($_GET['language']))
					$temp_lng->get_browser_language();
				$_SESSION['languages_id'] = $temp_lng->language['id'];

			}

			if (isset($_SESSION['language']) && !isset($_SESSION['language_charset'])) {
				$_SESSION['languages_id'] = $temp_lng->language['id'];

			}
		}


		// *******************************************************************
		// * PRODUCT 301 REDIRECT ********************************************
		// *******************************************************************

		// Create 301 redirect for PRODUCT LINKS without xtc:Suma friendly URLS
		// e.g.: http://www.shopname.com/product_info.php?info=p124_Produkt-1.html
		if ($fileName=='product_info.php' && $_GET['info']<>'' && $_GET['products_id']=='' && $_GET['action']=='') {
			$parameters='info='.$_GET['info'];
			$redirectLink = $this->getProductLink($parameters,$connection,$_SESSION['languages_id']);
			header("HTTP/1.1 301 Moved Permanently");
			header("Location: ".$redirectLink);
			die('This page has moved to: <a href="'.$redirectLink.'">'.$redirectLink.'</a>');
		}

		// Create 301 redirect for PRODUCT LINKS with xtc:Suma friendly URLS
		// e.g.: http://www.shopname.com/product_info.php/products_id/124
		if ($fileName=='product_info.php' && $_GET['products_id']<>'' && $_GET['info']==''&& $_GET['action']=='') {
			$parameters='products_id='.$_GET['products_id'];
			$redirectLink = $this->getProductLink($parameters,$connection,$_SESSION['languages_id']);

			header("HTTP/1.1 301 Moved Permanently");
			header("Location: ".$redirectLink);
			die('This page has moved to: <a href="'.$redirectLink.'">'.$redirectLink.'</a>');
		}

		// *******************************************************************
		// * CATEGORY 301 REDIRECT *******************************************
		// *******************************************************************

		// Create 301 redirect for CATEGORY LINKS without xtc:Suma friendly URLS
		// e.g.: http://www.shopname.com/index.php?cat=c10_Kategorie-5.html
		if ($fileName == 'index.php' && $_GET['cat']<>'' && $_GET['cPath']=='' && $_GET['page']=='' && $_GET['action']=='') {
			$parameters='cat='.$_GET['cat'];
			$redirectLink = $this->getCategoryLink($parameters,$connection,$_SESSION['languages_id']);
			header("HTTP/1.1 301 Moved Permanently");
			header("Location: ".$redirectLink);
			die('This page has moved to: <a href="'.$redirectLink.'">'.$redirectLink.'</a>');
		}

		// Create 301 redirect for CATEGORY LINKS with xtc:Suma friendly URLS (cPath)
		// e.g.: http://www.shopname.com/index.php/cPath/10
		if ($fileName == 'index.php' && $_GET['cPath']<>'' && $_GET['page']=='' && $_GET['action']=='') {

			// Extract last Category ID if subcategories are submitted
			// e.g.: http://www.shopname.com/index.php/cPath/77_78
			// (78 is the subcategory)
			$explodedCategoryParameters = explode('_',$_GET['cPath']);
			//$_GET['cPath']=end($explodedCategoryParameters);

			$parameters='cPath='.$_GET['cPath'];
			$redirectLink = $this->getCategoryLink($parameters,$connection,$_SESSION['languages_id']);
			header("HTTP/1.1 301 Moved Permanently");
			header("Location: ".$redirectLink);
			die('This page has moved to: <a href="'.$redirectLink.'">'.$redirectLink.'</a>');
		}

		// *******************************************************************
		// * CONTENT 301 REDIRECT ********************************************
		// *******************************************************************
		if ($fileName == 'shop_content.php' && $_GET['coID']<>'' && $_GET['action']=='') {
			$parameters='coID='.$_GET['coID'];
			$redirectLink = $this->getContentLink($parameters,$connection,$_SESSION['languages_id']);
			header("HTTP/1.1 301 Moved Permanently");
			header("Location: ".$redirectLink);
			die('This page has moved to: <a href="'.$redirectLink.'">'.$redirectLink.'</a>');
		}

		// *******************************************************************
		// * Blog-Item 301 REDIRECT ********************************************
		// *******************************************************************
		if ($fileName == 'blog.php' && $_GET['blog_item']<>'' && $_GET['delete_comment'] == '') {
			$parameters='blog_item='.$_GET['blog_item'];

			$redirectLink = $this->getBlogLink($parameters,$connection,$_SESSION['languages_id']);
			header("HTTP/1.1 301 Moved Permanently");
			header("Location: ".$redirectLink);
			die('This page has moved to: <a href="'.$redirectLink.'">'.$redirectLink.'</a>');
		}

		// *******************************************************************
		// * Blog-Cat 301 REDIRECT ********************************************
		// *******************************************************************
		if ($fileName == 'blog.php' && $_GET['blog_cat']<>'' && $_GET['blog_item']=='') {
			$parameters='blog_cat='.$_GET['blog_cat'];

			$redirectLink = $this->getBlogCatLink($parameters,$connection,$_SESSION['languages_id']);
			header("HTTP/1.1 301 Moved Permanently");
			header("Location: ".$redirectLink);
			die('This page has moved to: <a href="'.$redirectLink.'">'.$redirectLink.'</a>');
		}

		return false;

	}
}
?>